package com.action;

import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.multipart.MultipartFile;

import com.beans.StudentInfo;
import com.dao.StudentDao;
import com.utils.StrUtil;

@Controller
@RequestMapping("/student")
public class StudentExcelAction {
	@Resource
	StudentDao studentDao;

	@RequestMapping("/imortToDB")
	public String importToDB(HttpSession session, Model m) {
		List<StudentInfo> studentList = (List<StudentInfo>) session.getAttribute("studentList");
		studentDao.saveAll(studentList);
		m.addAttribute("msg", "数据导入成功!");
		session.removeAttribute("studentList");
		return "/admin/student/student_import.jsp";
	}

	@RequestMapping("/loadFromXls")
	public String ImportExcel(MultipartFile file1, HttpSession session) throws Exception {
		// 一个内部类
		class HeaderItem {
			HeaderItem(String fieldName, int x, int y) {
				this.fieldName = fieldName;
				this.x = x;
				this.y = y;
			}

			public String fieldName;
			public int x;
			public int y;
		}

		Map<String, HeaderItem> headerMap = new HashMap<>();

		Workbook workbook = WorkbookFactory.create(file1.getInputStream());

		Sheet sheet = workbook.getSheetAt(1);
		List<StudentInfo> studentList = new ArrayList<>();
		if (sheet != null) {
			// 先把表头读到map中
			Row headerRow = sheet.getRow(2);
			int headerCols = headerRow.getLastCellNum();// 表头总共的列数
			for (int j = 0; j < headerCols; j++) {
				Cell cell = headerRow.getCell(j);
				if (cell != null) {
					cell.setCellType(CellType.STRING);
					String headerValue = cell.getStringCellValue();
					HeaderItem item = new HeaderItem(headerValue, 2, j);
					headerMap.put(item.fieldName, item);
				}
			}

			int rowCount = sheet.getLastRowNum();// 得到excel的总记录条数

			// 从第三行才开始有数据
			for (int i = 3; i <= rowCount; i++) {// 遍历行
				StudentInfo stu = new StudentInfo();
				Row row = sheet.getRow(i);
				Field[] fields = StudentInfo.class.getDeclaredFields();

				for (Field field : fields) {
					String key = field.getName();

					if ("id".equals(key) ||"出生日期".equals(key)) {
						//在大表中出生日期是用身份证提取的
						continue;
					}

					HeaderItem item = headerMap.get(key);
					if (item == null) {
						System.out.println(key +" : 是空");
						continue;
					}
					int y = item.y; // 取到y轴坐标

					Cell cell = row.getCell(y);

					if (cell != null) {
						field.setAccessible(true);
						if (cell.getCellTypeEnum() == CellType.NUMERIC) {
							if (HSSFDateUtil.isCellDateFormatted(cell)) {
								//毕时业时属于这种情况
								field.set(stu, cell.getDateCellValue());
							}
							else {
								//电话,qq属于这种情况,用BigDecimal是为了处理乱码
								try {
									BigDecimal d=new BigDecimal(cell.getNumericCellValue());
									field.set(stu, d.toString());
								}
								catch(Exception ex) {
									System.out.println("出错:"+ex.getMessage());
								}
							}
						} else {

							try {
								cell.setCellType(CellType.STRING);
								field.set(stu, cell.getStringCellValue());
							} catch (Exception ex) {
								System.out.println("出错:"+ex.getMessage());
							}
						}
					}
				}

				//最后处理一下出生日期,把身份证号中的生日截取出来
				String idcard=stu.get身份证号();
				SimpleDateFormat df=new SimpleDateFormat("yyyyMMdd");
				if(!StrUtil.isNullOrEmpty(idcard)) {
					stu.set出生日期(df.parse(idcard.substring(6,14)));
				}
				
				studentList.add(stu);
			}
		}

		// 这里用session是为了让用户在点击导入数据库的时候取数据方便,
		session.setAttribute("studentList", studentList);
		return "/admin/student/student_import.jsp";
	}

	// 取消导入,要把session中的数据清除
	@GetMapping("/cancleImport")
	public String cancleImport(HttpSession session, Model m) {
		session.removeAttribute("studentList");
		m.addAttribute("msg", "已取消导入! ");
		return "/admin/student/student_import.jsp";
	}

	@RequestMapping("/exportToExcel")
	public void exportToExcepl(Integer[] ids, HttpSession session, HttpServletResponse response) throws IOException {
		List<StudentInfo> studentList = studentDao.findAllById(Arrays.asList(ids));
		// 读取模板
		//String templatePath = session.getServletContext().getRealPath("/admin/excel_template/tmp.xls");
		String templatePath ="c:/tmp.xls";
		HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(templatePath));

		Sheet sheet = workbook.getSheetAt(0);

		int rowIndex = 3; // 行的索引

		// 创建单元格样式
		CellStyle cell_style = workbook.createCellStyle();

		// 自定义字体颜色, 同单元格样式
		HSSFFont font = workbook.createFont();
		font.setFontHeightInPoints((short) 8);

		// 设置字体
		font.setFontName(HSSFFont.FONT_ARIAL);
		cell_style.setFont(font);

		for (int i = studentList.size() - 1; i >= 0; i--) {
			StudentInfo stu = studentList.get(i);
			Row nRow = sheet.createRow(rowIndex++);

			int cellIndex = -0;

			// 用户名
			Cell cell = nRow.createCell(cellIndex++);
			cell.setCellStyle(cell_style);
			cell.setCellValue(stu.get学员编号());

			nRow.createCell(cellIndex++).setCellValue(stu.get姓名());
			nRow.createCell(cellIndex++).setCellValue(stu.get校招());
			nRow.createCell(cellIndex++).setCellValue(stu.get状态());
			nRow.createCell(cellIndex++).setCellValue(stu.get班级());

			if (stu.get进班时间() != null) {
				nRow.createCell(cellIndex++).setCellValue(formatDate(stu.get进班时间()));
			} else {
				cellIndex++;
			}

			if (stu.get出班时间() != null) {
				nRow.createCell(cellIndex++).setCellValue(formatDate(stu.get出班时间()));
			} else {
				cellIndex++;
			}

			nRow.createCell(cellIndex++).setCellValue(stu.get咨询());
			nRow.createCell(cellIndex++).setCellValue(stu.get立项学费());
			nRow.createCell(cellIndex++).setCellValue(stu.get培养方式());
			nRow.createCell(cellIndex++).setCellValue(stu.get学生档案());
			nRow.createCell(cellIndex++).setCellValue(stu.get身份证号());
		//	nRow.createCell(cellIndex++).setCellValue(stu.get照片());
			cellIndex++;
			nRow.createCell(cellIndex++).setCellValue(stu.get学历证());
			nRow.createCell(cellIndex++).setCellValue(stu.get协议());
			nRow.createCell(cellIndex++).setCellValue(stu.get学员手册());
			nRow.createCell(cellIndex++).setCellValue(stu.get电话());
			nRow.createCell(cellIndex++).setCellValue(stu.getQQ());
			if (stu.get毕业时间() != null) {
				nRow.createCell(cellIndex++).setCellValue(formatDate(stu.get毕业时间()));
			} else {
				cellIndex++;
			}
			nRow.createCell(cellIndex++).setCellValue(stu.get毕业学校());
			nRow.createCell(cellIndex++).setCellValue(stu.get学历());
			nRow.createCell(cellIndex++).setCellValue(stu.get专业());
			nRow.createCell(cellIndex++).setCellValue(stu.get语种());
			nRow.createCell(cellIndex++).setCellValue(stu.get外语级别());
			nRow.createCell(cellIndex++).setCellValue(stu.get其他证书());
			nRow.createCell(cellIndex++).setCellValue(stu.get身份证号());
			if (stu.get出生日期() != null) {
				nRow.createCell(cellIndex++).setCellValue(formatDate(stu.get出生日期()));
			} else {
				cellIndex++;
			}

			nRow.createCell(cellIndex++).setCellValue(stu.get年龄());
			nRow.createCell(cellIndex++).setCellValue(stu.get性别());
			nRow.createCell(cellIndex++).setCellValue(stu.get民族());
			nRow.createCell(cellIndex++).setCellValue(stu.get政治面貌());
			nRow.createCell(cellIndex++).setCellValue(stu.get户口所在地());
			nRow.createCell(cellIndex++).setCellValue(stu.get家庭住址());
			nRow.createCell(cellIndex++).setCellValue(stu.get家庭电话());
			
			nRow.createCell(cellIndex++).setCellValue(stu.get联系人一());
			nRow.createCell(cellIndex++).setCellValue(stu.get联系人一电话());
			nRow.createCell(cellIndex++).setCellValue(stu.get联系人二());
			nRow.createCell(cellIndex++).setCellValue(stu.get联系人二电话());
			
			nRow.createCell(cellIndex++).setCellValue(stu.get班主任分析学员特点());

		}

		for (int i = 3; i < studentList.size() + 3; i++) {
			Row row = sheet.getRow(i);
			if (row != null) {
				for (int j = 0; j < row.getLastCellNum(); j++) {
					Cell cell = row.getCell(j);
					if (cell != null) {
						cell.setCellStyle(cell_style);
					}
				}
			}
		}

		// 证定自适应列宽
		for (int i = 0; i < 38; i++) {
		//	sheet.setColumnWidth(i, sheet.getColumnWidth(i));
			 // 调整每一列宽度                
			sheet.autoSizeColumn((short) i);                
			// 解决自动设置列宽中文失效的问题               
			sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
		}

	    // 最后，下载新增用户表，字节数组的输出流，它可存可取，带缓冲区
		ByteArrayOutputStream bos = new ByteArrayOutputStream();
		workbook.write(bos); // 将工作簿写到输出流中
	 // new DownloadUtil().download(bos,response,bigTitle+".xlsx");

		response.setContentType("application/octet-stream;charset=utf-8");
	 // returnName = response.encodeURL(new String(returnName.getBytes(),"iso8859-1")); //保存的文件名,必须和页面编码一致,否则乱码
		response.addHeader("Content-Disposition", "attachment;filename=" + "xxx.xls");
		response.setContentLength(bos.size());

		ServletOutputStream outputstream = response.getOutputStream(); // 取得输出流
		bos.writeTo(outputstream);
		bos.close(); 
		outputstream.flush();

		bos.close();
		workbook.close();

	}

	String formatDate(Date d) {
		SimpleDateFormat df = new SimpleDateFormat("yyyy/MM/dd");
		return df.format(d);
	}

}
